package com.kdx.cloud.common.utils;

import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;

import org.springframework.util.StringUtils;

/**
 * @author litianshen
 * @date 2017年1月11日 上午9:39:36
 * @Description:
 */
public class SqlBuilderUtil {
	

	public static String makePagingSQL(String sql, int offset, int limit) {
		StringBuilder pagingSelect = new StringBuilder(sql.length() + 100);
		pagingSelect.append(sql);
		if (offset <= 0) {
			if (limit >= Integer.MAX_VALUE) { // 读取所有，不需要Limit.
			} else {
				pagingSelect.append(" limit ").append(limit);
			}
		} else {
			pagingSelect.append(" limit ").append((offset)).append(", ").append(limit);
		}
		return pagingSelect.toString();
	}

	/**
	 * 这是给makeSqlCondition()方法用的
	 */
	private static HashMap<String, String> operatorMap = new HashMap<String, String>();
	static {
		operatorMap.put("eq", "=");
		operatorMap.put("gt", ">");
		operatorMap.put("lt", "<");
		operatorMap.put("ge", ">=");
		operatorMap.put("le", "<=");
		operatorMap.put("ne", "<>");
		operatorMap.put("like", " like ");
		operatorMap.put("in", " in ");
	}

	/**
	 * 将形如f.process_Buseness_Class:xx的字符串作为key填充入map中，其中xx的取值为eq,gt,lt,ge,le,ne,
	 * like,in。
	 * 其中f.n.表示map的value需要过滤(f:字符串类型,n:数值类型)，eq表示查询操作符为=，gt为>,lt为<,ge为>=,le为
	 * <=,ne为<>,like为like,in为in。 如果key以eq结尾并且value以%开头或结尾，执行like操作符
	 * 如果key以like结尾并且value以=开头，执行=操作 最后将查询结果返回<BR>
	 * 注:在source中使用${condition}替代符可以指定条件位置,以支持ORDER-BY,GROUP-BY
	 * 
	 * @param conditionMap
	 * @param source
	 * @return
	 */
	public static String makeSqlCondition(Map<String, String> conditionMap, String source) {
		return makeSqlCondition(conditionMap, source, true);
	}

	/**
	 * 功能与makeSqlCondition相同，额外增加自动添加where判断
	 * 
	 * @param conditionMap
	 * @param source
	 * @return
	 */
	public static String makeSqlCondition(Map<String, String> conditionMap, String source, boolean hasWhere) {
		StringBuffer sb = null;
		if (hasWhere) {
			sb = makeSqlCondition(conditionMap);
		} else {
			sb = getSqlConditionWithWhere(conditionMap);
		}
		if (source.indexOf("${condition}") > -1) {
			String s = source.substring(0, source.indexOf("${condition}"));
			String e = source.substring(source.indexOf("${condition}") + "${condition}".length());
			sb.insert(0, s);
			sb.append(" ").append(e);
		} else {
			sb.insert(0, source);
		}
		return sb.toString();
	}

	/**
	 * 有条件自动增加where，没有条件反馈空
	 * 
	 * @param conditionMap
	 * @return
	 */
	private static StringBuffer getSqlConditionWithWhere(Map<String, String> conditionMap) {
		StringBuffer sb = makeSqlCondition(conditionMap);
		if (sb.length() > 0) {
			return sb.replace(0, 4, " where "); // first ' and '
		}
		return sb;
	}

	private static StringBuffer makeSqlCondition(Map<String, String> conditionMap) {
		StringBuffer sb = new StringBuffer();

		for (Iterator<String> i = conditionMap.keySet().iterator(); i.hasNext();) {
			String key = i.next();
			String value = null;
			// String vi = String.valueOf(conditionMap.get(key));
			String vi = conditionMap.get(key);
			boolean isNull = false;
			if (vi != null) {
				value = vi.trim();
				if (vi.length() > 0 && value.length() == 0) {
					isNull = true;
				}
			}
			if (key.startsWith("f.") && (!StringUtils.isEmpty(value) || isNull)) {
				// 字符串：
				if (value.indexOf('\'') > -1) {
					value = value.replaceAll("\'", "\''");
				}

				// 字段名
				String fieldName = getFieldName(key);
				sb.append(" and ");
				if (isNull) {
					sb.append("(").append(fieldName).append(" IS NULL OR LENGTH(RTRIM(").append(fieldName)
							.append("))=0)");
					continue;
				} else {
					sb.append("(").append(fieldName);
				}

				// > < >= <= = <>...>>>>:eq,like,ge,le,in
				String operator = getOperator(key);
				if (operator.equals("in") || value.indexOf(',') > 0) {
					sb.append(" in ");
					sb.append("(");
					String[] vs = vi.split(",");
					for (int v = 0; v < vs.length; v++) {
						if (vs[v].indexOf('\'') > -1) {
							vs[v] = vs[v].replaceAll("\'", "");
						}
						sb.append('\'');
						sb.append(vs[v].trim());
						sb.append('\'');
						if (v < vs.length - 1) {
							sb.append(',');
						}
					}
					sb.append(")").append(")");
				} else {
					if (operator.equals("eq") && (value.startsWith("%") || value.endsWith("%"))) {
						sb.append(" like ");
					} else if (operator.equals("like") && value.startsWith("=")) {
						sb.append("=");
						operator = "eq";
						value = value.substring(1).trim();
					} else if (value.startsWith(">") || value.startsWith("<")) {
						if (value.length() == 1 || (value.charAt(1) == '=' && value.length() == 2)) {
							continue;
						}
						if (value.contains("&&")) {
							String[] vs = value.split("&&");
							for (int v = 0; v < vs.length; v++) {
								vs[v] = vs[v].trim();
								if (v != 0) {
									sb.append(fieldName);
								}
								if (vs[v].startsWith(">=") || vs[v].startsWith("<=")) {
									operator = vs[v].substring(0, 2);
									sb.append(operator);
									if (vs[v].length() > 2) {
										sb.append('\'').append(vs[v].substring(2)).append('\'');
									} else {
										sb.append("''");
									}
								} else if (vs[v].startsWith(">") || vs[v].startsWith("<")) {
									operator = vs[v].substring(0, 1);
									sb.append(operator);
									if (vs[v].length() > 1) {
										sb.append('\'').append(vs[v].substring(1)).append('\'');
									} else {
										sb.append("''");
									}
								} else {
									int x = value.charAt(2) == '=' ? 2 : 1;
									operator = value.substring(0, x);
									if (operator.equals(">")) {
										operator = "<";
									} else if (operator.equals("<")) {
										operator = ">";
									} else if (operator.equals("<=")) {
										operator = ">=";
									} else if (operator.equals(">=")) {
										operator = "<=";
									}
									sb.append(operator);
									sb.append('\'').append(vs[v]).append('\'');
								}
								if (v < vs.length - 1) {
									sb.append(" AND ");
								} else {
									sb.append(")");
								}
							}
							continue;
						} else {
							int x = value.charAt(2) == '=' ? 2 : 1;
							operator = value.substring(0, x);
							sb.append(operator);
							value = value.substring(x);
						}

					} else {
						sb.append(operatorMap.get(operator));
					}
					sb.append("'");
					if (operator.equals("like")) {
						sb.append("%").append(value).append("%");
					} else {
						sb.append(value);
					}
					sb.append("')");
				}
			}
			if (key.startsWith("n.") && !StringUtils.isEmpty(value)) {
				value = value.replaceAll(" ", "");
				String fieldName = getFieldName(key);
				String operator = getOperator(key);
				if (StringUtil.isNumeric(value)) {
					sb.append(" and ").append(fieldName);
					sb.append(operatorMap.get(operator));
					sb.append(value);
				} else {
					operator = value.replaceAll("[\\d\\.]", "");
					value = value.replaceAll("[\\>\\<\\=]", "");
					if (operator.length() > 0 && operatorMap.containsValue(operator) && operator.length() <= 2
							&& StringUtil.isNumeric(value)) {
						sb.append(" and ").append(fieldName);
						sb.append(operator);
						sb.append(value);
					}
				}
			}
		}
		return sb;
	}

	private static String getFieldName(String key) {
		String fieldName = key.substring(key.indexOf('.') + 1, key.indexOf(':'));
		return fieldName;
	}

	private static String getOperator(String key) {
		String operator = key.substring(key.indexOf(':') + 1);
		return operator;
	}
}
